package acceso.listener;

import acceso.hibernate.AccesoHibernate;
import acceso.util.Escritor;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

public class ConsultasMail {

    public void activaciones() {
        List l = new ArrayList();

        Connection oConn = null;

        Statement st = null;
        ResultSet rs = null;
        StringBuffer html = new StringBuffer();
        html.append("<h4 align=center>REPORTE DE ACTIVACION Y PAGOS</h4>");
        html.append("<table cellpadding=0 cellspacing=0 border=1 align=center>");
        html.append("<tr >");
        html.append("<td align=center>Fecha</td>");
        html.append("<td align=center>N Exp. Act. Motos</td>");
        html.append("<td align=center>N Exp. Act. Techos</td>");
        html.append("<td align=center>N Exp. Cro. Motos</td>");
        html.append("<td align=center>N Exp. Cro. Techos</td>");
        html.append("<td align=center>N Exp. Pag. Motos</td>");
        html.append("<td align=center>N Exp. Pag. Techos</td>");
        html.append("<td align=center>N Exp. Apl. Motos</td>");
        html.append("<td align=center>N Exp. Apl. Techos</td>");
        html.append("</tr>");
        String sql = "";
        try {
            oConn = AccesoHibernate.getSessionFactory().openStatelessSession().connection();
            st = oConn.createStatement();

            sql = "select tb.fe_produc, (select count(*) from credit.tbbwsgen where fe_activa = tb.fe_produc and co_produc = 1) as ca_actmot, (select count(*) from credit.tbbwsgen where fe_activa = tb.fe_produc and co_produc = 2) as ca_acttch, (select count(*) from credit.tbbwscro where co_contra in (select co_contra from credit.tbbwsgen where fe_activa = tb.fe_produc and co_produc = 1)) as ca_cromot, (select count(*) from credit.tbbwscro where co_contra in (select co_contra from credit.tbbwsgen where fe_activa = tb.fe_produc and co_produc = 2)) as ca_crotch, (select count(*) from credit.tbbwspag bp, credit.tbbwsgen bg where bp.fe_pagcuo = tb.fe_produc and bp.co_contra = bg.co_contra and bg.co_produc = 1) as ca_pagmot, (select count(*) from credit.tbbwspag bp, credit.tbbwsgen bg where bp.fe_pagcuo = tb.fe_produc and bp.co_contra = bg.co_contra and bg.co_produc = 2) as ca_pagtch, (select count(*) from credit.tbbwsapl bp, credit.tbbwsgen bg where bp.fe_pagcuo = tb.fe_produc and bp.co_contra = bg.co_contra and bg.co_produc = 1) as ca_aplmot, (select count(*) from credit.tbbwsapl bp, credit.tbbwsgen bg where bp.fe_pagcuo = tb.fe_produc and bp.co_contra = bg.co_contra and bg.co_produc = 2) as ca_apltch from (     select fe_activa as fe_produc from credit.tbbwsgen     where fe_activa >= current_date - interval '10 days'     group by fe_activa     union     select fe_pagcuo as fe_produc from credit.tbbwspag     where fe_pagcuo >= current_date - interval '10 days'     group by fe_pagcuo  ) tb  order by tb.fe_produc desc";

            Escritor.escribe_frawor("Inicio " + sql);
            rs = st.executeQuery(sql);
            String di_cenope = " ";
            String co_docide = "";
            while (rs.next()) {
                html.append("<tr>");
                html.append("<td align=right>" + rs.getString("fe_produc") + "</td>");
                html.append("<td align=right>" + rs.getString("ca_actmot") + "</td>");
                html.append("<td align=right>" + rs.getString("ca_acttch") + "</td>");
                html.append("<td align=right>" + rs.getString("ca_cromot") + "</td>");
                html.append("<td align=right>" + rs.getString("ca_crotch") + "</td>");
                html.append("<td align=right>" + rs.getString("ca_pagmot") + "</td>");
                html.append("<td align=right>" + rs.getString("ca_pagtch") + "</td>");
                html.append("<td align=right>" + rs.getString("ca_aplmot") + "</td>");
                html.append("<td align=right>" + rs.getString("ca_apltch") + "</td>");
                html.append("</tr>");
            }
            html.append("<table>");
            Escritor.escribe_frawor("Final " + sql);

            Connection oConn2 = AccesoHibernate.getSessionFactory().openStatelessSession().connection();
            Statement st2 = oConn2.createStatement();
            String sql2 = "select NEXTVAL('correo.qbcorreo')";
            ResultSet rs2 = st2.executeQuery(sql2);

            int id_correo = -1;
            if (rs2.next()) {
                id_correo = rs2.getInt(0);
            }

            sql2 = " insert into correo.tbcorreo ( id_correo, no_remite, em_remite, no_asunto, ti_estado, no_mensaj, ti_conten ) values (" + id_correo + "," + "      'Operador Sistemas'," + "      'operador@acceso.com.pe'," + "      'Activaciones diarias'," + "      'P'," + "      '" + html.toString() + "'," + "      'TH'" + " )";

            st2.executeUpdate(sql2);

            sql2 = " insert into correo.tbdestin (id_correo, no_destin, em_destin, ti_destin) values (" + id_correo + ", 'Sistemas', 'sistemas@acceso.com.pe', 'P')";

            st2.executeUpdate(sql2);
        } catch (Exception ex) {
            ex.printStackTrace();
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
                if (oConn != null) {
                    oConn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;
            oConn = null;
        }
    }

    public static void consultaArchivo(String co_adjunt, String sql, String il_compres) throws SQLException, Exception {
        List l = new ArrayList();

        Connection oConn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        File f = new File("/tmp/archivotemp.txt");
        f.delete();
        BufferedWriter bwr = new BufferedWriter(new FileWriter("/tmp/archivotemp.txt"));

        oConn = AccesoHibernate.getSessionFactory().openStatelessSession().connection();
        ps = oConn.prepareStatement(sql);
        Escritor.escribe_frawor("Inicio " + sql);
        rs = ps.executeQuery();
        String contenido = "";
        while (rs.next()) {
            contenido = rs.getString(1);
            bwr.write(contenido);
            bwr.newLine();
        }

        bwr.close();
        Escritor.escribe_frawor("Final " + sql);
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (oConn != null) {
                oConn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        rs = null;
        ps = null;
        oConn = null;
        if (il_compres.equals("S")) {
            File fa = new File("/tmp/archivotemp.zip");
            fa.delete();

            byte[] buf = new byte[10485760];
            try {
                ZipOutputStream out = new ZipOutputStream(new FileOutputStream("/tmp/archivotemp.zip"));

                FileInputStream in = new FileInputStream("/tmp/archivotemp.txt");

                out.putNextEntry(new ZipEntry(co_adjunt));

                int len = 0;
                while ((len = in.read(buf)) > 0) {
                    out.write(buf, 0, len);
                }

                out.closeEntry();
                in.close();

                out.close();
            } catch (IOException e) {
            }
        }
    }
}
